# database.py
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine, Column, Integer, String, Float, Text, Date

# 创建数据库引擎
SQLALCHEMY_DATABASE_URL = "sqlite:///./data/data.sqlite"
engine = create_engine(SQLALCHEMY_DATABASE_URL, connect_args={
                       "check_same_thread": False})

# 创建会话
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)


Base = declarative_base()


# 让ORM管理好这些字段
# 定义好模型，数据库自动生成


class Product(Base):
    """
    产品信息模型
    """
    __tablename__ = 'products'
    # product_id
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(255), nullable=False)  # 产品名称
    market = Column(String(100), nullable=False)  # 市场区域(东南亚/北美/欧洲)
    category = Column(String(100), nullable=False)  # 产品类别(小家电/健身器材/家居用品)
    img_url = Column(String(255))  # 图片URL
    price = Column(Float, nullable=False)  # 价格(RMB)
    description = Column(Text)  # 产品描述
    hot_index = Column(Integer)  # 热门指数

    def __repr__(self):
        return f"<Product(name='{self.name}', market='{self.market}', price={self.price})>"


class ProductSpecification(Base):
    """
    产品规格参数模型
    """
    __tablename__ = 'product_specifications'

    id = Column(Integer, primary_key=True, autoincrement=True)
    product_id = Column(Integer, nullable=False)  # 关联产品ID
    parameter_name = Column(String(100), nullable=False)  # 参数名称
    parameter_value = Column(String(255), nullable=False)  # 参数值


class HotIndexHistory(Base):
    """
    产品热门指数历史数据模型
    """
    __tablename__ = 'hot_index_history'

    id = Column(Integer, primary_key=True, autoincrement=True)
    product_id = Column(Integer, nullable=False)  # 关联产品ID
    period = Column(String(50), nullable=False)  # 时间段(如: 2010-2014)
    index_value = Column(Integer, nullable=False)  # 指数值


def create_database(database_url):
    """
    创建数据库和表
    """
    engine = create_engine(database_url)
    Base.metadata.create_all(engine)
    return engine


def get_session(engine):
    """
    获取数据库会话
    """
    Session = sessionmaker(bind=engine)
    return Session()


Base.metadata.create_all(bind=engine)


def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()
